package com.ddy.test.boxiao;

import com.ddy.test.NumberFormatTools;
import com.ddy.test.excel.ExcelUtils;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by ddy on 2017/11/30.
 */
public class SZWXData {

    static String excel = "/Users/ddy/Documents/yun-szwx/新都二中/新都区第二中学用户信息.xlsx";

    public static void main(String[] args) throws Exception {
        //convertCardNo();
        createSZWXUSerData(4,"学生信息");
        createSZWXUSerData(1,"老师信息");
        createSZWXClassData("班级数据");
        createSZWXCLassUser();//班级用户关系

    }

    //四中网校班级用户数据导入
    public static void createSZWXCLassUser() throws Exception {


        //List<List<String>> stuList = readXlsx("/Users/ddy/Documents/yun-szwx/新都二中/新都区第二中学用户信息.xlsx","学生信息");

        List<List<String>> teaList = ExcelUtils.readXlsx(excel,"老师信息");
        Map<String,String> teaIdNameMap = new HashMap<String,String>();
        for(List<String> list : teaList){
            teaIdNameMap.put(list.get(0),list.get(2));

        }
        List<List<String>> classList = ExcelUtils.readXlsx(excel,"班级数据");
        Map<String,String> classIdNameMap = new HashMap<String,String>();
        Map<String,String> classIdGradeMap = new HashMap<String,String>();
        for(List<String> list : classList){
            classIdGradeMap.put(list.get(0),list.get(1));
            classIdNameMap.put(list.get(0),list.get(2));

        }
        List<List<String>> classUserList = ExcelUtils.readXlsx(excel,"班级人员关系");
        System.out.println("-- init hr_department_teacher");
        System.out.println("insert into bxn.hr_department_teacher \n" +
                "\t(createdDate, lastUpdated, deleted, departmentId, importDate, \n" +
                "\tpositionName, \n" +
                "\tpositionType, \n" +
                "\tsequence, \n" +
                "\tuser_id\n" +
                "\t)\n" +
                "\tvalues");
        for(List<String> list : classUserList){
            String user_id = list.get(1);
            String type = list.size()>2?list.get(2):"";
            String year = list.size()>3?list.get(3):"";

            if("2016~2017".equals(year)){//2016~2017
                continue;
            }
            if(!"学生".equals(type)){
                System.out.println("(NOW(),NOW(),0,2,NOW(),NULL,1," + user_id + "," + user_id + "),");
            }

        }

        Map<String, String> szwxStuParData = getSZWXStuParData(4, "学生信息");
        System.out.println("\n-- init sys_organization_user");
        System.out.println("insert into bxn.sys_organization_user \n" +
                "\t(position, createdDate, lastUpdated, organization_id, user_id, \n" +
                "\tseq, \n" +
                "\tpositionType, \n" +
                "\tposition_relation_id\n" +
                "\t)\n" +
                "\tvalues");
        //学生家长关联关系
        for(List<String> list : classUserList){
            String class_id = list.get(0);
            String user_id = list.get(1);
            String type = list.size()>2?list.get(2):"";
            String year = list.size()>3?list.get(3):"";
            String subject_id = list.size()>4?list.get(4):"";
            String subject_name = list.size()>5?list.get(5):"";

            if("2016~2017".equals(year)){//2016~2017
                continue;
            }
            if(!szwxStuParData.containsKey(user_id)){
                continue;
            }
            if("学生".equals(type)){
                System.out.println("('学生',NOW(),NOW()," + class_id + "," + user_id + "," + user_id + ",3,NULL),");
                String parId = szwxStuParData.get(user_id);
                System.out.println("('家长',NOW(),NOW()," + class_id + "," + parId + "," + parId + ",2,NULL),");
            }else{
                System.out.println("('教师',NOW(),NOW(),402," + user_id + "," + user_id + ",1,NULL),");
            }

        }

        System.out.println("\n-- init tbs_student_clazz");
        System.out.println("insert into bxn.tbs_student_clazz \n" +
                "\t(createdDate, lastUpdated, deleted, sequence, sn, clazz_id, student_uid\n" +
                "\t)\n" +
                "\tvalues");
        for(List<String> list : classUserList){
            String class_id = list.get(0);
            String user_id = list.get(1);
            String type = list.size()>2?list.get(2):"";
            String year = list.size()>3?list.get(3):"";
            String subject_id = list.size()>4?list.get(4):"";
            String subject_name = list.size()>5?list.get(5):"";

            if("2016~2017".equals(year)){//2016~2017
                continue;
            }
            if(!szwxStuParData.containsKey(user_id)){
                continue;
            }
            if("学生".equals(type)){
                System.out.println("(NOW(),NOW(),0,NULL,NULL," + class_id + "," + user_id  + "),");
            }
        }

        System.out.println("\n=================================教师分班信息");
        for(List<String> list : classUserList){
            String class_id = list.get(0);
            String user_id = list.get(1);
            String type = list.size()>2?list.get(2):"";
            String year = list.size()>3?list.get(3):"";
            String subject_id = list.size()>4?list.get(4):"";
            String subject_name = list.size()>5?list.get(5):"";

            if("2016~2017".equals(year)){//2016~2017
                continue;
            }
            if(!"学生".equals(type)){
                System.out.println(classIdGradeMap.get(class_id) + " " + classIdNameMap.get(class_id) + "(" + class_id + ") " + type + ": " + subject_name + "("  + subject_id + ") ==> " + teaIdNameMap.get(user_id) + "(" + user_id + ")");
            }

        }

    }

    //四中网校学生教师数据导入
    public static Map<String, String> getSZWXStuParData(int type, String sheetName) throws Exception {
        List<List<String>> lists = ExcelUtils.readXlsx(excel,sheetName);
        int parId=1005000;
        Map<String,String> stuParMap = new HashMap<String,String>();
        for(List<String> rowList : lists){
            String stuId = rowList.get(0);
            stuParMap.put(stuId,(parId++)+"");
        }
        return stuParMap;
    }


    //四中网校班级数据导入
    public static void createSZWXClassData(String sheetName) throws Exception {
        Map<String,String> orgIdMap = new HashMap<String,String>();
        orgIdMap.put("初一","1104");
        orgIdMap.put("初二","1103");
        orgIdMap.put("初三","1102");
        orgIdMap.put("高一","1204");
        orgIdMap.put("高二","1203");
        orgIdMap.put("高三","1202");
        Map<String,String> classTypeMap = new HashMap<String,String>();
        classTypeMap.put("初一","20");
        classTypeMap.put("初二","20");
        classTypeMap.put("初三","20");
        classTypeMap.put("高一","40");
        classTypeMap.put("高二","40");
        classTypeMap.put("高三","40");
        Map<String,String> gradeTermMap = new HashMap<String,String>();
        gradeTermMap.put("初一","10091");
        gradeTermMap.put("初二","10087");
        gradeTermMap.put("初三","10083");
        gradeTermMap.put("高一","20091");
        gradeTermMap.put("高二","20087");
        gradeTermMap.put("高三","20083");

        List<List<String>> lists = ExcelUtils.readXlsx(excel,sheetName);
        System.out.println("-- init tbs_clazz");
        System.out.println("insert into bxn.tbs_clazz \n" +
                "\t(id, createdDate, lastUpdated, clazzCode, clazzLabel, clazzType, deleted, \n" +
                "\tdescription, \n" +
                "\tpreClazzId, \n" +
                "\tsequence, \n" +
                "\tsn, \n" +
                "\tunclassify, \n" +
                "\tgradeterm_id\n" +
                "\t)\n" +
                "\tvalues");
        for(List<String> list : lists){
            if("2016~2017".equals(list.get(4))){//2016~2017
                continue;
            }
            //bxn.tbs_clazz
            //(id, createdDate, lastUpdated, clazzCode, clazzLabel, clazzType, deleted,
            //description, preClazzId, sequence, sn, unclassify, gradeterm_id)
            System.out.println("(" + list.get(0) + ",NOW(),NOW(),NULL,'" + list.get(2) + "'," + classTypeMap.get(list.get(1)) + ",0," +
                    "NULL,NULL,NULL,NULL,0," + gradeTermMap.get(list.get(1)) + "),");

        }
        System.out.println("\n-- init sys_organization");
        System.out.println("insert into bxn.sys_organization \n" +
                "\t(id, category, deleted, name, pinYin, windowPhone, parent_id, type, \n" +
                "\tactive, \n" +
                "\tcreatedDate, \n" +
                "\tlastUpdated, \n" +
                "\tseq, \n" +
                "\tinternalOrg, \n" +
                "\tsource_id\n" +
                "\t)\n" +
                "\tvalues");
        for(List<String> list : lists){
            if("2016~2017".equals(list.get(4))){
                continue;
            }
            //(id, category, deleted, name, pinYin, windowPhone, parent_id, type,
            System.out.println("(" + list.get(0) + ",2,0,'" + list.get(2) + "',NULL,NULL," + orgIdMap.get(list.get(1)) + ",4," +
                    //active, createdDate, lastUpdated, seq, internalOrg, source_id) values
                    "1,NOW(),NOW(),0,1,'CL_" + list.get(0) + "'),");

        }
    }

    //四中网校学生教师数据导入
    public static void createSZWXUSerData(int type,String sheetName) throws Exception {
        List<List<String>> lists = ExcelUtils.readXlsx(excel,sheetName);
        int i = 1,id=1005000;
        System.out.println("insert into bxn.sys_user \n" +
                "\t(id, active, category, cellPhone, deleted, email, fullname, pinYin, locked, password, \n" +
                "\tsettings, \n" +
                "\tsn, \n" +
                "\tuniqueNo, \n" +
                "\tusername, \n" +
                "\tportrait, \n" +
                "\tcreatedDate, \n" +
                "\tlastUpdated, \n" +
                "\tinternalUser, \n" +
                "\tsource_id, \n" +
                "\tparent_relation_id, \n" +
                "\tcellPhone2, \n" +
                "\tcellPhone3, \n" +
                "\tasePassword, \n" +
                "\tschoolCard, \n" +
                "\twechat\n" +
                "\t)\n" +
                "\tvalues");
        for(List<String> rowList : lists){
            String code = "2017" + type + NumberFormatTools.format(i++);
            //        id,    active, category, cellPhone, deleted, email, fullname, pinYin, locked, password,
            System.out.print("(" + rowList.get(0) + ",1," + type + ",'',0,NULL,'" + rowList.get(2) + "',NULL,0,'753628',");
            //        settings,sn, uniqueNo, username, portrait, createdDate, lastUpdated, internalUser,
            System.out.print("NULL,NULL,'" + code +"','" + rowList.get(1) + "',NULL,NOW(),NOW(),1," );
            //        source_id, parent_relation_id, cellPhone2, cellPhone3, asePassword, schoolCard, wechat
            System.out.println("NULL," + id + ",NULL,NULL,NULL,NULL,NULL),");
            if(type == 4){
                String parCode = "par" + code;
                System.out.print("(" + id++ + ",1,3,'',0,NULL,'" + rowList.get(2) + "的家长',NULL,0,'753628',");
                //        settings,sn, uniqueNo, username, portrait, createdDate, lastUpdated, internalUser,
                System.out.print("NULL,NULL,'" + parCode +"','par" + rowList.get(1) + "',NULL,NOW(),NOW(),1," );
                //        source_id, parent_relation_id, cellPhone2, cellPhone3, asePassword, schoolCard, wechat
                System.out.println("NULL," + rowList.get(0) + ",NULL,NULL,NULL,NULL,NULL),");
            }
        }
    }

}
